<?php
namespace SDXApi\Controller;

use Think\Controller;
use Think\Model;

use SDXApi\Model\UsersModel;

/**
 * 统计开放接口类
 * 
 * @author ^2_3^
 *
 */
class StatisticsapiController extends Controller{
    
    public function __construct() {
        parent::__construct();
        
        header('Access-Control-Allow-Origin: *');
    }
    
    /**
     * sayhello
     * http://apidev.cyb.cn/index.php?s=/statisticsapi/sayhello
     * @param ^2_3^
     */
    public function sayhello() {
        echo 'hello, ^2_3^! this is statisticsapi.';
    }
    
    /**
     * 生产随机字串(下载Excel表格)
     * http://apidev.cyb.cn/index.php?s=/statisticsapi/get_str4excel
     * http://api.shidexian.com/index.php?s=/statisticsapi/get_str4excel
     */
    public function get_str4excel() {
        set_time_limit(0);

        import('Vendor.PHPExcel.PHPExcel');
        import('Vendor.PHPExcel.PHPExcel.Reader.Excel5');
        import('Vendor.PHPExcel.PHPExcel.IOFactory');
    
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(125);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','随机字串');

        $idx = 50000;
        $i = 0;
        while ( $i <= $idx ){
            ++$i;
            
            $str = 'oUSQDs';
            $str .= self::get_random_str(22);
            
            $num = $i+1;
            $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A' . $num, $str);
            
        }
    
        $objPHPExcel->getActiveSheet()->setTitle( "随机字串" );
        $objPHPExcel->setActiveSheetIndex(0);
        
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=shidexian_radom_str.xls");
        header('Cache-Control: max-age=0');
        $write = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $write->save('php://output');
    
    }
    /**
     * 随机字符串
     * 产生随机字符串，不长于32位
     * @param int $length
     * @return 产生的随机字符串
     */
    private static function get_random_str($length = 32)
    {
        $chars = "abcdefghijklmnopqrstuvwxyz0123456789_-";
        $str ="";
        for ( $i = 0; $i < $length; $i++ )  {
            $str .= substr($chars, mt_rand(0, strlen($chars)-1), 1);
        }
        return $str;
    }
    
    /**
     * [完善订单配送区域数据] 更新订单经纬度/配送区域
     * http://apidev.cyb.cn/index.php?s=/statisticsapi/update_order_info
     * @author ^2_3^
     */
    private function update_order_info() {
        set_time_limit(0);
        $start = microtime(true);
    
        // 日期开始
        $date_begin_str = I('request.date_begin_str', '', 'trim');
        // 日期结束
        $date_end_str = I('request.date_end_str', '', 'trim');
        // 上次记录ID
        $last_id = I('request.last_id', 0, 'int');
        empty($last_id) && $last_id = 0;
        // 上上次记录ID
        $uoi_lastid = S('uoi_lastid_'.$date_begin_str);
        //-/ 数据验证
        if( empty($date_begin_str) || empty($date_end_str) || empty($last_id) ) {
            returnApiError('提示:参数缺失;');
    
        }else if( isset($uoi_lastid) && $last_id <= $uoi_lastid) {
            returnApiError('提示:数据已处理, 请进行下轮数据;');
        }
        $uoi_lastid_str = empty($uoi_lastid) ? "准备开始 [{$date_begin_str}_{$date_end_str}]" :
            "{$uoi_lastid} [{$date_begin_str}_{$date_end_str}]";
w_log('region_lng_lat_result', $uoi_lastid_str);
        S('uoi_lastid_'.$date_begin_str, $last_id, 600);
    
        /// 读取数据
        $arr_oi = self::get_order_data($date_begin_str, $date_end_str, $last_id);
        $num = count($arr_oi);
        if( $num == 0 ) {
            returnApiError('提示:已经处理完;');
        }
        $data['num'] = $num;
        $data['num_msg'] = ("处理记录总数: {$num}");
    
        /// 循环处理(地址转经纬度[get_lng_lat]; 经纬度匹配区[UserModel/get_distribution_by_gps]); 循环更新;
        $num_fail = 0;
        $num_region_fail = 0;
        $idx = 0;
        foreach ( $arr_oi as $i_key=>$i_arr ){
            ++$idx;
            if( $idx == $num ) {
                $data['lastid'] = $i_arr['order_id'];
                $data['lastid_msg'] = ("lastid: {$i_arr['order_id']}");
            }
    
            //-/ 详细地址_处理
            $address = str_replace( array($i_arr['province_name'], $i_arr['city_name'], $i_arr['district_name']),
                array('', '', ''), $i_arr['address']);
    
            /// 地址经度纬度
            $address = $address_short = trim($address);
            $lng_address_short = S('lng_'.$address_short);
            $lat_address_short = S('lat_'.$address_short);
            if( empty( $lng_address_short ) || empty( $lat_address_short ) ) {
                $address = $i_arr['province_name'].$i_arr['city_name'].$i_arr['district_name'].$address;
                $arr_lng_lat = get_lng_lat( $address ,$i_arr['city_name']);
                
                //-/ 地址缓存
                if( !empty($arr_lng_lat['lng']) && !empty($arr_lng_lat['lat']) ) {
                    S('lng_'.$address_short, $arr_lng_lat['lng'], 600);
                    S('lat_'.$address_short, $arr_lng_lat['lat'], 600);
                }
                
                if( empty($arr_lng_lat['lng']) || empty($arr_lng_lat['lat']) ) {
                    ++$num_fail;
                    w_log('region_lng_lat_log', "{$date_begin_str}_{$date_end_str} ".
                        $i_arr['order_id'].' '.$i_arr['order_sn'].' '.$address);
                    continue;
                }
                
            }else {
                $arr_lng_lat['lng'] = S('lng_'.$address_short);
                $arr_lng_lat['lat'] = S('lat_'.$address_short);
            }

            /// 经纬度匹配区
            $m_users = new UsersModel();
            $arr_region = $m_users->get_distribution_by_gps($arr_lng_lat['lat'], $arr_lng_lat['lng']);
            if( empty($arr_region['wdr_id']) ) {
                ++$num_region_fail;
w_log('region_lng_lat_region_log', "{$date_begin_str}_{$date_end_str} ".
    $i_arr['order_id'].' '.$i_arr['order_sn'].' '.
    $arr_lng_lat['lng'].' '.$arr_lng_lat['lat'].' '.$address.' '.json_encode($arr_region));
                continue;
            }
    
            /// 更新订单数据;
            $where_oi_u = "
            referer = '专场'
            AND wdr_id = 0
            AND order_id = {$i_arr['order_id']}
            ";
            $data_oi_u = array(
                "wdr_id" => $arr_region['wdr_id'],
                "longitude" => $arr_lng_lat['lng'],
                "latitude" => $arr_lng_lat['lat']
            );
            $flag_u = M('order_info')->where( $where_oi_u )->save( $data_oi_u );
w_log( 'region_lng_lat_oi_log', "{$date_begin_str}_{$date_end_str} ".
    $i_arr['order_id'].'   '.$i_arr['order_sn'] );
        }
    
        $num_ok = $num - $num_fail;
        $data['num_ok'] = $num_ok;
        $data['num_ok_msg'] = "经纬度转换成功: {$num_ok}";
        $data['num_fail'] = $num_fail;
        $data['num_fail_msg'] = "经纬度转换失败: {$num_fail}";
    
        $data['num_region_fail'] = $num_region_fail;
        $data['num_region_fail_msg'] = "经纬度转配送区失败: {$num_region_fail}";
        $num_region_ok = $num_ok - $num_region_fail;
        $data['num_region_ok'] = $num_region_ok;
        $data['num_region_ok_msg'] = "经纬度转配送区成功: {$num_region_ok}";
    
        $end = microtime(true);
        $time = $end - $start;
        $data['time'] = $time;
w_log('region_lng_lat_result', "{$date_begin_str}_{$date_end_str} ".json_encode($data));
        returnApiSuccess($data, 'ok');
    }
    
    /**
     * [完善订单配送区域数据] 获取订单数据
     * @param string $date_begin_str
     * @param string $date_end_str
     * @return array $arr_oi
     * @author ^2_3^
     */
    private function get_order_data( $date_begin_str = '2017-07-01', $date_end_str = '2017-08-01', $last_id = 0 ) {
        $arr_oi = array();
    
        $where_oi = "
            oi.pay_status >= 2
            AND oi.order_status IN (1, 5)
            AND oi.shipping_status = 2
            AND u.user_name LIKE 'wx_196%'
            AND oi.referer = '专场'
            AND oi.address != '活动现场'
            AND oi.add_time >= (UNIX_TIMESTAMP('{$date_begin_str}') - 8 * 3600)
            AND oi.add_time < (UNIX_TIMESTAMP('{$date_end_str}') - 8 * 3600)
            AND oi.wdr_id = 0
        ";
        if( !empty($last_id) ) {
            $where_oi = "{$where_oi} AND oi.order_id > {$last_id}";
        }
        $arr_oi = M('order_info oi')
            ->field('
                oi.order_id,
            	oi.order_sn,
            	oi.country,
            	oi.province,
            	rp.region_name AS province_name,
            	oi.city,
            	rc.region_name AS city_name,
            	oi.district,
            	rd.region_name AS district_name,
            	oi.address,
            	oi.wdr_id,
            	oi.longitude,
            	oi.latitude
            ')
            ->join('sdx_users u ON u.user_id = oi.user_id', 'left')
            ->join('sdx_region rp ON rp.region_id = oi.province', 'left')
            ->join('sdx_region rc ON rc.region_id = oi.city', 'left')
            ->join('sdx_region rd ON rd.region_id = oi.district', 'left')
            ->where( $where_oi )
            ->limit('0, 1000')
            ->select();
        $str_sql = M()->getLastSql();
w_log('region_sql_log', "{$date_begin_str}_{$date_end_str} {$str_sql}");
    
        return $arr_oi;
    }
    
    /**
     * [完善订单配送区域数据] 地址匹配完善
     * NOTE: 通过 地址匹配, 查找线上相同地址订单数据来补全;
     * 
     * http://apidev.cyb.cn/index.php?s=/statisticsapi/address_match_complete
     * @author ^2_3^
     */
    private function address_match_complete() {
        set_time_limit(0);
        $start = microtime(true);
        
        // 日期开始
        $date_begin_str = I('request.date_begin_str', '', 'trim');
        // 日期结束
        $date_end_str = I('request.date_end_str', '', 'trim');
        // 上次记录ID
        $last_id = I('request.last_id', 0, 'int');
        if( empty($date_begin_str) || empty($date_end_str) || empty($last_id) ) {
            returnApiError('提示:参数缺失;');
        }
        
        /// 读取数据
        $arr_oi = self::get_order_data($date_begin_str, $date_end_str, $last_id);
        $record_num = count($arr_oi);
        if( $record_num == 0 ) {
            returnApiError('提示:记录为空,已经处理完;');
        }
        $data['record_num'] = $record_num;
        $data['record_num_msg'] = "记录数: {$record_num};";
        
        $num_find = 0;
        $num_nfind = 0;
        $num_ufind = 0;
        $idx = 0;
        foreach ( $arr_oi as $i_key=>$i_arr ){
            ++$idx;
            if( $idx == $record_num ) {
                $data['lastid'] = $i_arr['order_id'];
                $data['lastid_msg'] = "上次记录ID: {$i_arr['order_id']};";
            }
            
            $where_oi = array(
                'referer' => array('IN', array('Android', 'iOS', 'Touch')),
                'country' => $i_arr['country'],
                'province' => $i_arr['province'],
                'city' => $i_arr['city'],
                'district' => $i_arr['district'],
                'address' => $i_arr['address'],
                'wdr_id' => array('NEQ', 0),
                'longitude' => array('NEQ', ''),
                'latitude' => array('NEQ', '')
            );
            $info_oi = M('order_info')->field('
                    order_id,
                	order_sn,
                	country,
                	province,
                	city,
                	district,
                	address,
                    wdr_id,
                	longitude,
                	latitude
                ')
                ->where( $where_oi )
                ->find();
            $sql_f = M()->getLastSql();
w_log('address_match_complete_sql_f_'.$date_begin_str, (empty($info_oi)?0:1)." 上次记录ID{$last_id} ".$sql_f);

            if( !empty($info_oi) ) {
                ++$num_find;
                
                $data_u_oi = array(
                    'wdr_id' => $info_oi['wdr_id'],
                    'longitude' => $info_oi['longitude'],
                    'latitude' => $info_oi['latitude']
                );
                $where_u_oi = array(
                    'order_id' => $i_arr['order_id'],
                    'referer' => '专场'
                );
                $flag_oi = M('order_info')->where($where_u_oi)->save( $data_u_oi );
                $sql_u = M()->getLastSql();
w_log('address_match_complete_sql_u_'.$date_begin_str, (empty($sql_u)?0:1)." 上次记录ID{$last_id} ".$sql_u);
                
                $flag_oi && ++$num_ufind;

            }else {
                ++$num_nfind;
            }
            
        }
        
        $data['num_nfind'] = $num_nfind;
        $data['num_nfind_msg'] = "未匹配数: {$num_nfind};";
        
        $data['num_find'] = $num_find;
        $data['num_find_msg'] = "匹配数: {$num_find};";
        
        $data['num_ufind'] = $num_ufind;
        $data['num_ufind_msg'] = "更新数: {$num_ufind}";
        
        $num_nupdate = $num_find - $num_ufind;
        $data['num_nupdate'] = $num_nupdate;
        $data['num_nupdate_msg'] = "更新失败: {$num_nupdate};";
        
        sleep(2);
        $end = microtime(true);
        $data['time'] = $end - $start;
        returnApiSuccess($data, 'ok');
    }
    
}